Relational databases can be effective for a wide variety of transaction processing applications. They are also particularly advantageous for their ability to support non-procedural data access for ad hoc queries and set-oriented (multiple row at a time) processing and eliminating the need for applications to navigate physical record structures on disk.
However, unlike other database management system approaches, relational database technology (including the SQL data access language) does not broadly support direct access to data based on a physical sequence of records (rows), nor does it broadly support successive accesses to a record (row) based on a “current position” in a table of rows. Instead, accessing a single row of a large table (to update or delete it for example) generally requires that the database management system process an index to locate the row of interest based on query criteria. This index-based access requires reading and processing (usually multiple) blocks containing index nodes to locate pointers to rows of interest, which is followed by a read request to bring the block containing the row into memory.
Positional access is commonly required by high-performance applications that require data access in a more structured, ordered fashion. For example, in processing queues of various kinds, a “first-in/last-out” sequence of data access is required. A software application that is monitoring real-time events might want to process such events in “first-in/first-out” sequence. Other applications might require that data be processed in sequence based on the value of one or more attributes of the data (e.g., a running total might be computed in “line item number” order). Designers of such applications often require predictable high performance, and must generally avoid sorts, and would prefer to use mechanisms other than indexes to locate data (especially sequentially valued rows). Locating data by indexes can be expensive (resource wise) because many times multiple indexes must be accessed to locate the data of interest. Similarly, they would choose where possible to avoid designing applications that obtain, save and use physical row pointers at the programming level, especially where the database system cannot guarantee that such row pointers are reliable over time.